﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Text;
using Microsoft.Data.SqlClient;
using DBUtil.Provider.SqlServer.MetaData;
using DBUtil.MetaData;
using System.Linq;
using System.Collections.Concurrent;
using System.Threading;
using System.Diagnostics;
using DotNetCommon;
using DotNetCommon.Extensions;
using System.Threading.Tasks;
using System.Reflection;
using System.ComponentModel.DataAnnotations.Schema;

namespace DBUtil.Provider.SqlServer
{
    /// <summary>
    /// Microsoft SQL Server操作对象
    /// </summary>
    public partial class SqlServerAccess : DBAccess
    {
        #region Is系列: 判断表/视图/列/存储过程是否存在
        /// <summary>
        /// 判断存储过程是否存在
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public async override Task<bool> IsProcedureExistAsync(string procName, CancellationToken cancellationToken = default(CancellationToken))
        {
            string sql = $"SELECT count(1) FROM sysobjects WHERE name='{DealSqlInject(procName)}' and xtype='P'";
            int r = await SelectScalarAsync<int>(sql, cancellationToken);
            return r > 0;
        }

        /// <summary>
        /// 判断触发器是否存在
        /// </summary>
        /// <param name="triggerName">触发器名称</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public async override Task<bool> IsTriggerExistAsync(string triggerName, CancellationToken cancellationToken = default(CancellationToken))
        {
            string sql = $"SELECT count(1) FROM sys.triggers WHERE name = N'{DealSqlInject(triggerName)}'";
            int r = await SelectScalarAsync<int>(sql, cancellationToken);
            return r > 0;
        }
        #endregion

        #region 基于数据库的Id和流水号生成器
        /// <summary>
        /// 根据指定的表名和列名生成Id
        /// </summary>
        /// <param name="tableName">指定的表名</param>
        /// <param name="colName">指定的列名</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        protected async override Task<long> InternalNewIdAsync(string tableName, string colName, CancellationToken cancellationToken = default(CancellationToken))
        {
            EnsureInitGenerator();
            var id = await SelectScalarAsync<long>($"exec {Settings.DBCacheGeneratorIdProcedureName} \"{tableName}\",\"{colName}\"", cancellationToken);
            return id;
        }

        /// <summary>
        /// 根据指定的表名和列名批量生成Id
        /// </summary>
        /// <param name="tableName">指定的表名</param>
        /// <param name="colName">指定的列名</param>
        /// <param name="count">生成的Id的数量</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        protected async override Task<List<long>> InternalNewIdsAsync(string tableName, string colName, int count, CancellationToken cancellationToken = default(CancellationToken))
        {
            if (count < 1) throw new Exception("批量生成的数量最小为1!");
            EnsureInitGenerator();
            var id = await SelectScalarAsync<long>($"exec {Settings.DBCacheGeneratorIdProcedureName} \"{tableName}\",\"{colName}\",{count}", cancellationToken);
            var res = new List<long>();
            for (long i = id - count + 1; i <= id; i++)
            {
                res.Add(i);
            }
            return res;
        }

        /// <summary>
        /// 根据表名和列名生成流水号
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="colName">列名</param>
        /// <param name="format">流水号格式</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        protected async override Task<string> InternalNewSNOAsync(string tableName, string colName, SerialFormat format, CancellationToken cancellationToken = default(CancellationToken))
        {
            SerialFormat.ValidFormat(format);
            EnsureInitGenerator();
            var (likestr, snoNow, startindex) = SerialFormat.Parse(format, null, DotNetCommon.Machine.MachineIdString);
            long no = await SelectScalarAsync<long>($"exec {Settings.DBCacheGeneratorSNOProcedureName} @tablename='{tableName}',@colname='{colName}',@nowstr='{snoNow.ToString("yyyy-MM-dd HH:mm:ss")}', @likestr='{likestr}',@startindex={startindex}", cancellationToken);
            var chunk = format.Chunks.FirstOrDefault(i => i.Type == SerialFormatChunkType.SerialNo);
            var sno = likestr.Substring(0, startindex);
            var s = no.ToString();
            if (s.Length > chunk.Length)
            {
                sno += s;
            }
            else
            {
                sno += s.PadLeft(chunk.Length, '0');
            }
            return sno;
        }

        /// <summary>
        /// 根据表名和列名批量生成流水号
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="colName">列名</param>
        /// <param name="format">流水号格式</param>
        /// <param name="count">生成的流水号的数量</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        protected async override Task<List<string>> InternalNewSNOsAsync(string tableName, string colName, SerialFormat format, int count, CancellationToken cancellationToken = default(CancellationToken))
        {
            if (count < 1) throw new Exception("批量生成的数量最小为1!");
            SerialFormat.ValidFormat(format);
            EnsureInitGenerator();
            var (likestr, snoNow, startindex) = SerialFormat.Parse(format, null, DotNetCommon.Machine.MachineIdString);
            long no = await SelectScalarAsync<long>($"exec {Settings.DBCacheGeneratorSNOProcedureName} @tablename='{tableName}',@colname='{colName}',@nowstr='{snoNow.ToString("yyyy-MM-dd HH:mm:ss")}', @likestr='{likestr}',@startindex={startindex},@count={count}", cancellationToken);
            var chunk = format.Chunks.FirstOrDefault(i => i.Type == SerialFormatChunkType.SerialNo);
            var res = new List<string>();
            for (long i = no - count + 1; i <= no; i++)
            {
                var sno = likestr.Substring(0, startindex);
                var s = i.ToString();
                if (s.Length > chunk.Length)
                {
                    sno += s;
                }
                else
                {
                    sno += s.PadLeft(chunk.Length, '0');
                }
                res.Add(sno);
            }
            return res;
        }
        #endregion
    }
}
